﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;

namespace mpsalaryprojectgroup.App_Code
{
    public class Wages
    {
        public String Year { get; set; }
        public String Year2 { get; set; }
        public String Course { get; set; }
        public String PolyConducting { get; set; }
        public String FreshGradsEmpNum { get; set; }
        public String FreshGradsEmpRate { get; set; }
        public String FreshGradsMonthlyMean { get; set; }
        public String FreshGradsMonthlyMean2 { get; set; }
        public String FreshGradsMonthlyMedian { get; set; }
        public String FreshGradsMonthlyMedian2 { get; set; }
        public String PostGradsEmpNum { get; set; }
        public String PostGradsEmpRate { get; set; }
        public String PostGradsMonthlyMean  { get; set; }
        public String PostGradsMonthlyMean2 { get; set; }
        public String PostGradsMonthlyMedian { get; set; }
        public String PostGradsMonthlyMedian2 { get; set; }
        public String IsIndustrial { get; set; }

        //public Wages(string Year, string FreshGradsMonthlyMean, string PostGradsMonthlyMean)
        //{
        //    this.PostGradsMonthlyMean = PostGradsMonthlyMean;
        //    this.Year = Year;
        //    this.FreshGradsMonthlyMean = FreshGradsMonthlyMean;
        //}

        //---------------------------------
        //Get Mean Wages
        //---------------------------------
        DataTable dt = new DataTable();

        public DataTable GetMeanWages()
        {
            if (dt.Columns.Count == 0)
            {
                dt.Columns.Add("Year");
                dt.Columns.Add("Fresh Graduates Mean");
                dt.Columns.Add("Post-NS Graduates Mean");
                dt.Columns.Add("Fresh Graduates Mean 2");
                dt.Columns.Add("Post-NS Graduates Mean 2");
               
            }
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                //2.open connection -> command
                conn.Open();
                comm.Connection = conn;

                comm.CommandText = "SELECT Year,FreshGradsMonthlyMean,PostGradsMonthlyMean From [Wages] Where Course = @Course ";
                comm.Parameters.AddWithValue("@Course", Course);

                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {
                    Year = dr["Year"].ToString();
                    FreshGradsMonthlyMean = dr["FreshGradsMonthlyMean"].ToString();
                    PostGradsMonthlyMean = dr["PostGradsMonthlyMean"].ToString();
                    dt.Rows.Add(Year, FreshGradsMonthlyMean, PostGradsMonthlyMean, "0", "0");
                }


            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;
            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }

         
            return dt;
        }

        //-----------------------------
        //Get Mean Wages for comparison of 2 courses
        //------------------------------

        public DataTable GetCompareMean()
        {

         
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                //2.open connection -> command
                conn.Open();
                comm.Connection = conn;

                comm.CommandText = "SELECT Year,FreshGradsMonthlyMean,PostGradsMonthlyMean From [Wages] Where Course = @Course ";
                comm.Parameters.AddWithValue("@Course", Course);

                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {
                   
                    Year2 = dr["Year"].ToString();
                    FreshGradsMonthlyMean2 = dr["FreshGradsMonthlyMean"].ToString();
                    PostGradsMonthlyMean2 = dr["PostGradsMonthlyMean"].ToString();

                    DataRow[] foundRows = dt.Select("Year = " + Year2);
                    if (foundRows.Length > 0)
                    {
                        foundRows[0][3] = FreshGradsMonthlyMean2;
                        foundRows[0][4] = PostGradsMonthlyMean2;
                    }
                    else
                    {
                        dt.Rows.Add(Year2, "0", "0", FreshGradsMonthlyMean2, PostGradsMonthlyMean2);
                    }


                }


            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;
            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }
            return dt;


        }



        //-----------------------------
        //get Median Wages
        //-----------------------------
        DataTable dt1 = new DataTable();
        public DataTable GetMedianWages()
        {

            if (dt1.Columns.Count == 0)
            {
                dt1.Columns.Add("Year");
                dt1.Columns.Add("Fresh Graduates Median");
                dt1.Columns.Add("Post-NS Graduates Median");
                dt1.Columns.Add("Fresh Graduates Median 2");
                dt1.Columns.Add("Post-NS Graduates Median 2");

            }

            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                //2.open connection -> command
                conn.Open();
                comm.Connection = conn;

                comm.CommandText = "SELECT Year,FreshGradsMonthlyMedian,PostGradsMonthlyMedian From [Wages] Where Course = @Course ";
                comm.Parameters.AddWithValue("@Course", Course);

                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {
                    Year = dr["Year"].ToString();
                    FreshGradsMonthlyMedian = dr["FreshGradsMonthlyMedian"].ToString();
                    PostGradsMonthlyMedian = dr["PostGradsMonthlyMedian"].ToString();
                    dt1.Rows.Add(Year, FreshGradsMonthlyMedian, PostGradsMonthlyMedian,"0","0");
                }


            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;
            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }
          
            return dt1;
        }




        //-----------------------------
        //Get Median Wages for comparison of 2 courses
        //------------------------------

        public DataTable GetCompareMedian()
        {


            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                //2.open connection -> command
                conn.Open();
                comm.Connection = conn;

                comm.CommandText = "SELECT Year,FreshGradsMonthlyMedian,PostGradsMonthlyMedian From [Wages] Where Course = @Course ";
                comm.Parameters.AddWithValue("@Course", Course);

                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {

                    Year2 = dr["Year"].ToString();
                    FreshGradsMonthlyMedian2 = dr["FreshGradsMonthlyMedian"].ToString();
                    PostGradsMonthlyMedian2 = dr["PostGradsMonthlyMedian"].ToString();

                    DataRow[] foundRows = dt1.Select("Year = " + Year2);
                    if (foundRows.Length > 0)
                    {
                        foundRows[0][3] = FreshGradsMonthlyMedian2;
                        foundRows[0][4] = PostGradsMonthlyMedian2;
                    }
                    else
                    {
                        dt1.Rows.Add(Year2, "0", "0", FreshGradsMonthlyMedian2, PostGradsMonthlyMedian2);
                    }


                }


            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;
            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }
            return dt1;


        }


       

        


    }
}